"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : 系统视图PG_PREPARED_STATEMENT
Case Name   : 关闭session 删除预备语句，不可查询到已删除预备语句
Create At   : 2023/11/30
Owner       : opentestcase022
Description :
    1.建表
    2.创建预备语句
    3.使用PG_PREPARED_STATEMENT函数查询
    4.关闭session删除预备语句
    5.新建session,使用PG_PREPARED_STATEMENT函数查询
    6.清理环境
Expect      :
    1.执行成功
    2.执行成功
    3.执行成功，可查询到stmt1
    4.执行成功
    5.执行成功,执行成功，查询结果中为空
    6.执行成功
History     :
"""

import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class PgPreparedStatement3(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        text = f'-----{os.path.basename(__file__)} start-----'
        self.log.info(text)
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.t_name = 't_prepare_03'
        self.prepare_name = 'stmt1'

    def test_1(self):
        text = 'step1:建表; expect:执行成功'
        self.log.info(f'-----{text}-----')
        sql = f'drop table if exists {self.t_name};' \
            f'create table {self.t_name}(c1 int, c2 text);'
        self.log.info(sql)
        res = self.pri_sh.execut_db_sql(sql)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, res, f'执行失败：{text}')

        text = 'step2:创建预备语句; expect:执行成功'
        self.log.info(f'-----{text}-----')
        prepare_sql = f"prepare {self.prepare_name} as " \
            f"insert into {self.t_name} values(1, 'openGauss');"
        self.log.info(prepare_sql)

        text = 'step3:使用PG_PREPARED_STATEMENT函数查询; expect:执行成功，可查询到stmt1'
        self.log.info(f'-----{text}-----')
        query_sql = 'select * from pg_prepared_statement() order by 1, 2;'
        self.log.info(prepare_sql + query_sql)
        res = self.pri_sh.execut_db_sql(prepare_sql + query_sql)
        self.log.info(res)
        self.assertIn(self.prepare_name, res, f'执行失败：{text}')

        text = 'step4:关闭session删除预备语句; expect:执行成功'
        self.log.info(f'-----{text}-----')
        self.log.info('因第3步是gsql -c执行的，执行结束即关闭session，故此处无需操作')

        text = 'step5:新建sessionm,使用PG_PREPARED_STATEMENT函数查询; ' \
               'expect:执行成功，查询结果中为空'
        self.log.info(f'-----{text}-----')
        self.log.info(query_sql)
        res = self.pri_sh.execut_db_sql(query_sql)
        self.log.info(res)
        self.assertNotIn(self.prepare_name, res, f'执行失败：{text}')

    def tearDown(self):
        self.log.info('----run teardown----')
        text = 'step6:清理环境; expect:执行成功'
        self.log.info(f'-----{text}-----')
        sql = f'drop table if exists {self.t_name};'
        res = self.pri_sh.execut_db_sql(sql)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, res, f'执行失败：{text}')
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
